/*
	exec sp_accrued_liability_report_proc 'fab','0','0','0','0','0','fab','0','0'
*/
create proc sp_accrued_liability_report_proc

@psType		varchar(50),
@productId	varchar(50),
@vendorId	varchar(50),
@lotCategory	varchar(50),
@fromDate	varchar(50),
@toDate		varchar(50),
@psTypeName	varchar(50),
@reportType	varchar(50),
@defaultPrompt	varchar(50)

as
begin
	declare @sqlstring varchar(4000)
	declare @current_quarter varchar(10)
	declare @current_year varchar(10)

	set @current_quarter = convert(varchar,datepart(q,current_timestamp))
	set @current_year = convert(varchar,datepart(yyyy,current_timestamp))

	create table #Results 
		( ProcessStepId int,[Lot Number] varchar(50),[Fab Lot Number] varchar(50),[Lot Category] varchar(50),
		  [Wafer Qty In] int,[Die Qty In] int,[Date In] datetime,[Standard Cost] numeric(18,5),[Part Number] varchar(50),
		  [Base Product] varchar(50),[Product Order] int,[Process] varchar(50),[Process Order] int,Vendor varchar(50) )
	
	set @sqlstring = 'Insert into #Results
	select 
		lt.processstep_id,lt.lot_number,lt.fab_lot_number,lt.lot_category,Wafer_qty_in,qty_in,date_in,
		( case 
			when '''+@current_quarter+''' = 1 then ( select Q1_standard_cost from update_view_cost_table where datepart(yyyy,fin_year) = '''+@current_year+''' and processstep_id = lt.processstep_id )
			when '''+@current_quarter+''' = 2 then ( select Q2_standard_cost from update_view_cost_table where datepart(yyyy,fin_year) = '''+@current_year+''' and processstep_id = lt.processstep_id )
			when '''+@current_quarter+''' = 3 then ( select Q3_standard_cost from update_view_cost_table where datepart(yyyy,fin_year) = '''+@current_year+''' and processstep_id = lt.processstep_id )
			when '''+@current_quarter+''' = 3 then ( select Q4_standard_cost from update_view_cost_table where datepart(yyyy,fin_year) = '''+@current_year+''' and processstep_id = lt.processstep_id )
		end),
		p.part_number,
		(SELECT PART_NUMBER FROM PRODUCT WHERE PRODUCT_ID = 
			CASE 
				WHEN ( P.P_PARENT_PRODUCT_ID IS NULL OR P.P_PARENT_PRODUCT_ID = -1 ) THEN P.PRODUCT_ID 
				WHEN P.P_PARENT_PRODUCT_ID IS NOT NULL THEN P.P_PARENT_PRODUCT_ID	
			END 
		) AS BASE_PRODUCT,
		(case 
			when p_type is null then 1
			when p_type = ''Board'' then 2
			when p_type = ''kit'' then 3
			else 4
		end),
		ps_type,
		(case 
			when ps_type_name = ''fab'' then 1
			when ps_type_name = ''sort'' then 2
			when ps_type_name = ''assembly'' then 3
			when ps_type_name = ''test'' then 4
			else 11
		end),l_vendor_name
	from 
		lot_table lt,product p,process_step ps, location l
	where 
		lt.processstep_id = p.processstep_id 
		and p.processstep_id = ps.processstep_id
		and lt.location_id = l.location_id
		and ( lt.po_receipt_id is null or lt.po_receipt_id <= 0 )
		and ( Wafer_qty_in > 0 or qty_in > 0 )
		and lot_current_status = ''close'' 
		and ( ps_type_name =''fab'' or ps_type_name = ''sort'' or ps_type_name = ''assembly'' or ps_type_name = ''test'' ) '

	IF @psType = '0'
	BEGIN
		IF ( @productId IS NOT NULL AND @productId <> '0' )
		BEGIN
			SET @sqlstring = @sqlstring + ' AND P.P_PARENT_PRODUCT_ID IN ( ( SELECT PROCESSSTEP_ID FROM FN_GET_PROCESS_STEPS_IN_STRING( '''+@productId+''' ) )  )'
		END	
	END
	ELSE--IF @PROCESS_TYPE <> '0'
	BEGIN
		SET @sqlstring = @sqlstring + ' AND PS.PS_TYPE = '''+@psType+''''

		IF ( @productId IS NOT NULL AND @productId <> '0' )
		BEGIN
			SET @sqlstring = @sqlstring + ' AND P.PROCESSSTEP_ID IN ( ( SELECT PROCESSSTEP_ID FROM FN_GET_PROCESS_STEPS_IN_STRING( '''+@productId+''' ) )  )'
		END
	END
	
	IF ( @vendorId IS NOT NULL AND @vendorId <> '0' )
	BEGIN
		SET @sqlstring = @sqlstring + ' AND vendor_id = '''+@vendorId+''''
	END
	IF ( @lotCategory IS NOT NULL AND @lotCategory <> '0' )
	BEGIN
		SET @sqlstring = @sqlstring + ' AND LT.LOT_CATEGORY = '''+@lotCategory+''''
	END
	
	IF ( @fromDate IS NOT NULL AND @fromDate <> '0' )
	BEGIN
		SET @sqlstring = @sqlstring +' AND date_in >= '''+ CONVERT(VARCHAR,@fromDate, 101)+ ' 12:00AM ' +''''
	END
	IF ( @toDate IS NOT NULL AND @toDate <> '0' )
	BEGIN
		SET @sqlstring = @sqlstring + +' AND date_in <= '''+ CONVERT(VARCHAR,@TODATE, 101)+ ' 11:59PM ' +''''
	END

	--print '@sqlstring = '+@sqlstring
	EXEC (@sqlstring)

	if @reportType = 'xlreport'
	begin
		select 
			[Base Product],[Part Number],[Fab Lot Number],[Lot Number],Vendor ,[Process],
			[Date In],[Wafer Qty In],[Die Qty In],
			( isnull([Wafer Qty In],[Die Qty In]) * [Standard Cost] ) as [Standard Cost],[Lot Category]
		from 
			#Results
		order by
			[Product Order],[Base Product],[Process Order],[Process],[Part Number],[Lot Number]
	end
	else
	begin
		select 
			ProcessStepId,[Lot Number],[Fab Lot Number],[Lot Category],[Wafer Qty In],[Die Qty In],
			[Date In],( isnull([Wafer Qty In],[Die Qty In]) * [Standard Cost] ) as [Standard Cost],
			[Part Number],[Base Product],[Product Order],[Process],[Process Order],Vendor 
		from 
			#Results
		order by
			[Product Order],[Base Product],[Process Order],[Process],[Part Number],[Lot Number]
	end
end 
